from django.http import JsonResponse
from django.shortcuts import render
from database.connection import Link_database
from global_file import MOSTPERMISSIONS
from global_file import SELJIANLI
from Sendemail import Email

from oa.extension import WorkPlanSQL
from oa.extension import ResumeSQL
# from oa.extension import del_WorkPlanSQL
from oa.extension import OpenPermissionsSQL
from oa.extension import WorkDetailsSQL

from oa.take_data import all_user_id_list
from oa.take_data import alls
from oa.take_data import select_user
from oa.take_data import ResumeSel
from oa.take_data import WorkPlanSel
from oa.take_data import sel_work_plan
from oa.take_data import AllWorkPlanSel
from oa.take_data import AllWorkPlanSel
from oa.take_data import work_details
from oa.take_data import open_permissions

from caiwu.views import hostname

# Create your views here.
from django.views import View
import datetime
import time
import logging
import random
import json
import global_file
import xlrd

logger = logging.getLogger(__name__)


def UserInfoHandle(user):
    """ 用户数据处理 """

    UserList = []
    # for user in alluser:
    UserDict = {}
    UserDict['user_id'] = user[0]  # 用户id
    UserDict['name'] = user[1]  # 用户真实姓名
    UserDict['gender'] = user[3]  # 性别
    UserDict['born_date'] = user[4]  # 出生日期
    UserDict['height'] = user[5]  # 身高
    UserDict['weight'] = user[6]  # 体重
    UserDict['ethnic'] = user[7]  # 民族
    UserDict['polittical'] = user[8]  # 政治面貌
    UserDict['marriage'] = user[9]  # 婚否
    UserDict['id_card'] = user[10]  # 身份证
    UserDict['phone'] = user[11]  # 手机号码
    UserDict['emergency_phone'] = user[12]  # 紧急联系人
    UserDict['hk_adderss'] = user[13]  # 户口地址
    UserDict['jt_address'] = user[14]  # 家庭住址
    UserDict['xz_address'] = user[15]  # 现居地
    UserDict['learn'] = user[16]  # 学历
    UserDict['professional'] = user[17]  # 专业
    UserDict['graduation_data'] = user[18]  # 毕业时间
    UserDict['school'] = user[19]  # 毕业学校
    UserDict['members'] = user[20]  # 家庭成员
    UserDict['jobs'] = user[21]  # 岗位
    UserDict['department'] = user[22]  # 部门ID
    UserDict['permissions'] = user[23]  # 权限
    UserDict['induction_date'] = user[24]  # 入职时间
    UserDict['positive_date'] = user[25]  # 转正时间
    UserDict['departure_date'] = user[26]  # 离职时间
    UserDict['bank_name'] = user[27]  # 开户银行
    UserDict['bank'] = user[28]  # 银行账号
    UserDict['state'] = user[29]  # 状态
    UserList.append(UserDict)
    return UserList


# def GetUserId(jobs,userid,Cursor):

# 	""" 不同管理职位，返回不同员工信息 """

# 	findid_sql = """ select user_id from depar_user where %s = %d """  %(jobs,userid)  #通过主管id查找部门的同事
# 	Cursor.execute(findid_sql)
# 	all_userid = Cursor.fetchall()
# 	userid_list = []
# 	for i in all_userid:
# 		userid_list.append(i[0])
# 	if len(userid_list) == 1:
# 		userid_list.append(0) #确保元祖元素不为1
# 	tup_id = tuple(userid_list)

# 	# 查找用户信息
# 	userinfo_sql = """ select * from user where id IN {} """.format(tup_id)
# 	Cursor.execute(userinfo_sql)
# 	all_user = Cursor.fetchall()
# 	return all_user


class UserView(View):
    """用户视图"""

    def get(self, request):
        return JsonResponse({"status": True, 'result': 1})


class UserLongin(View):
    """ 用户登录 """

    def post(self, request):

        UserName = request.POST.get('username')
        PassWd = request.POST.get('passwd')
        DB, Cursor = Link_database()
        CheckName_Sql = """ select * from user where name = '%s' """ % (UserName)
        Cursor.execute(CheckName_Sql)
        if Cursor.fetchone():
            CheckUser_Sql = """ select * from user where name = '%s' and password = '%s' """ % (UserName, PassWd)
            Cursor.execute(CheckUser_Sql)
            if Cursor.fetchone():
                result = "登陆成功！"
                code = 200
                # 用户信息
                userinfo_sql = """ select id,name,gender,department,jobs from user where name ='%s' """ % (UserName)
                Cursor.execute(userinfo_sql)
                userinfo = Cursor.fetchone()

                # 权限信息
                Asset_registration_Sql = """ select permission_data,checked from permission_table where user_id = %d """ % (
                    userinfo[0])  # 通过用户id查找权限
                Cursor.execute(Asset_registration_Sql)
                id_list = Cursor.fetchone()
                sideNavData = id_list[0]
                checked = id_list[1]
                data = {"user_id": userinfo[0], "user_name": userinfo[1], "gender": userinfo[2],
                        "department_id": userinfo[3], "jobs": userinfo[4],
                        "sideNavData": json.loads(sideNavData), "checked": json.loads(checked)}

            else:
                result = "密码错误！"
                code = 400
                data = []
        else:
            result = "该用户不存在！"
            code = 400
            data = []
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": data})


class UpdataPwd(View):
    """ 修改密码 """

    def post(self, request):

        UserName = request.POST.get('username')

        PassWd = request.POST.get('passwd')

        DB, Cursor = Link_database()

        CheckName_Sql = """ select * from user where name = '%s' """ % (UserName)

        Cursor.execute(CheckName_Sql)

        if Cursor.fetchone():

            UpdataPwd_Sql = """ UPDATE user SET password = '%s' WHERE  name = '%s' """ % (PassWd, UserName)

            Cursor.execute(UpdataPwd_Sql)

            DB.commit()

            result = "修改完成！"
            code = 200

        else:

            result = "该用户不存在！"
            code = 400

        DB.close()

        Cursor.close()

        return JsonResponse({"code": code, "result": result, "data": []})


class PersonalInformation(View):
    """ 通过传入一个用户id,返回用户信息 """

    def post(self, request):

        userid = int(request.POST.get('userid'))
        DB, Cursor = Link_database()

        userinfo_sql = """ select * from user where id = %d """ % (userid)
        Cursor.execute(userinfo_sql)
        all_user = Cursor.fetchone()
        if all_user:
            data = UserInfoHandle(all_user)
            result = "成功"
            code = 200
        else:
            code = 400
            result = "不存在该用户!"
            data = []
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": data})


class AdjustJob(View):
    """ 岗位调整 """

    def post(self, request):

        job_name = request.POST.get('job_name')  # 调岗名称
        department_id = int(request.POST.get('department_id'))
        adjust_salary = request.POST.get('salary')  # 薪资
        adjust_time = request.POST.get('adjust_time')  # 调整时间
        user_id = int(request.POST.get('user_id'))  # 用户id
        social_security = request.POST.get('social_security')  # 社保信息
        director_general = request.POST.get('director_general')  # 所属总监名称
        supervisor = request.POST.get('supervisor')  # 所属主管名称

        DB, Cursor = Link_database()
        CheckUser_Sql = """ select * from user where id = %d """ % (user_id)
        Cursor.execute(CheckUser_Sql)
        if Cursor.fetchone():
            # 修改用户表
            UpdataUser_Sql = """ UPDATE user SET  jobs = '%s',department = %d  WHERE  id = %d """ % (
                job_name, department_id, user_id)
            Cursor.execute(UpdataUser_Sql)
            DB.commit()

            # 数据插入调岗信息表
            inset_sql = """ INSERT INTO  adjust_posts
			(job_name,salary,adjust_time,user_id,social_security) 
			VALUES ('%s','%s','%s',%d,'%s') """ % (job_name, adjust_salary, adjust_time, user_id, social_security)
            Cursor.execute(inset_sql)
            DB.commit()

            # 岗位从属级插入

            #	通过名称查找总监主管id
            CheckUser_Sql = """ select id from user where name in ('%s','%s') """ % (director_general, supervisor)
            Cursor.execute(CheckUser_Sql)
            data = Cursor.fetchall()
            if len(data) == 2:  # 有总监有主管

                UpdataUser_Sql = """ UPDATE depar_user SET  department = %d,director = %d, tube = %d WHERE  user_id = %d  """ % (
                    department_id, data[1][0], data[0][0], user_id)
                Cursor.execute(UpdataUser_Sql)
                DB.commit()
            elif len(data) == 1:  # 只有主管
                UpdataUser_Sql = """ UPDATE depar_user SET  department = %d,director = %d, tube = %d WHERE  user_id = %d  """ % (
                    department_id, data[0][0], 0, user_id)
                Cursor.execute(UpdataUser_Sql)
                DB.commit()

            # 修改换岗权限数据
            # 1.根据部门获取权限数据
            Sec_Sql = """ select checked,sidenavdata from depar where id=%d  """ % (department_id)
            Cursor.execute(Sec_Sql)
            Per_data = Cursor.fetchone()
            checked, sideNavData = Per_data[0], Per_data[1]
            # 2.修改用户权限
            Per = OpenPermissionsSQL(user_id, checked, sideNavData)
            Per.main()
            code = 200
            result = "完成！"

        else:
            code = 400
            result = "不存在该用户!"

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})

        """
            1.修改用户表用户岗位信息，部门信息
            2.将调岗信息保存在调岗信息表里面
            3.


         """


class Attendance(View):
    """ 考勤录入统计 """

    def post(self, request):
        user_id = int(request.POST.get('user_id'))  # 用户id
        be_attendance_days = request.POST.get('be_attendance_days')  # 应出勤天数
        attendance_days = request.POST.get('attendance_days')  # 实出勤天数
        work_overtime = request.POST.get('work_overtime')  # 加班天数
        leave_record = request.POST.get('leave_record')  # 请假记录
        completion_record = request.POST.get('completion_record')  # 旷工记录
        late_time = request.POST.get('late_time')  # 迟到时长
        business_travel_day = request.POST.get('business_travel_day')  # 出差天数
        time = request.POST.get('time')  # 统计月份时间段

        DB, Cursor = Link_database()
        # 获取已记录本月的用户考勤数据
        CheckUser_Sql = """ select * from attendance where user_id = %d and statistics_time = '%s' """ % (user_id, time)
        Cursor.execute(CheckUser_Sql)
        if Cursor.fetchone():
            code = 400
            result = "该用户已经录入了考勤数据，请勿重复录入!"
        else:
            inset_sql = """ INSERT INTO  attendance
			(user_id,be_attendance_days,attendance_days,work_overtime,leave_record,completion_record,late_time,business_travel_day,statistics_time) 
			VALUES (%d,'%s','%s','%s','%s','%s','%s','%s','%s') """ % (
            user_id, be_attendance_days, attendance_days, work_overtime, leave_record, completion_record, late_time,
            business_travel_day, time)
            Cursor.execute(inset_sql)
            DB.commit()
            code = 200
            result = "录入完成!"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class UpdateAttendance(View):
    """ 修改考勤记录 """

    def post(self, request):
        id = int(request.POST.get('id'))  # 数据id
        # user_id = int(request.POST.get('user_id'))  # 用户id
        be_attendance_days = request.POST.get('be_attendance_days')  # 应出勤天数
        attendance_days = request.POST.get('attendance_days')  # 实出勤天数
        work_overtime = request.POST.get('work_overtime')  # 加班天数
        leave_record = request.POST.get('leave_record')  # 请假记录
        completion_record = request.POST.get('completion_record')  # 旷工记录
        late_time = request.POST.get('late_time')  # 迟到时长
        business_travel_day = request.POST.get('business_travel_day')  # 出差天数
        time = request.POST.get('time')  # 统计月份时间段

        DB, Cursor = Link_database()
        Update_Sql = """ UPDATE attendance SET be_attendance_days = '%s',attendance_days = '%s',work_overtime='%s',leave_record='%s',completion_record='%s',late_time='%s',business_travel_day='%s',statistics_time ='%s' WHERE id=%d """ % (
            be_attendance_days, attendance_days, work_overtime, leave_record, completion_record, late_time,
            business_travel_day, time, id)
        try:
            Cursor.execute(Update_Sql)
            DB.commit()
            code = 200
            result = "修改成功！"
        except:
            code = 400
            result = "修改失败，数据库操作失败！"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class GetAttendance(View):
    """ 获取全部考勤 """

    def post(self, request):
        time = request.POST.get('time')  # 统计月份时间段
        DB, Cursor = Link_database()

        Attendance_Sql = """ select a.id,a.be_attendance_days,a.attendance_days,a.work_overtime,a.leave_record,a.completion_record,a.late_time,a.business_travel_day,a.statistics_time,b.name  from attendance as a left join user as b on a.user_id=b.id where statistics_time='%s' """ % (
            time)
        Cursor.execute(Attendance_Sql)
        AttendanceData = Cursor.fetchall()
        AttendanceList = []
        for ad in AttendanceData:
            AttendanceList.append(
                {"id": ad[0], "be_attendance_days": ad[1], "attendance_days": ad[2], "work_overtime": ad[3],
                 "leave_record": ad[4], "completion_record": ad[5],
                 "late_time": ad[6], "business_travel_day": ad[7], "statistics_time": ad[8], "name": ad[9]})

        if AttendanceList != []:
            code = 200
            result = "成功！"
        else:
            code = 400
            result = "当前月份没有数据！"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": AttendanceList})


class AssetRegistration(View):
    """ 公司资产录入 """

    def post(self, request):
        device_name = request.POST.get('device_name')  # 设备名称
        device_stock = int(request.POST.get('device_stock'))  # 设备数量
        is_use = int(request.POST.get('is_use'))  # 使用数量
        remarks = request.POST.get('remarks')  # 备注

        DB, Cursor = Link_database()
        CheckName_Sql = """ select * from asset_registration where name = '%s' """ % (device_name)
        Cursor.execute(CheckName_Sql)
        if Cursor.fetchone():
            # 更改数量情况

            Updata_Sql = """ UPDATE asset_registration SET stock = %d,is_use = %d ,remarks = '%s' WHERE  name = '%s' """ % (
                device_stock, is_use, remarks, device_name)
            Cursor.execute(Updata_Sql)
            DB.commit()
            result = "更改成功！"
        else:
            # 新增设备
            inset_sql = """ INSERT INTO  asset_registration
			(name,	stock,is_use,remarks) 
			VALUES ('%s',%d,%d,'%s') """ % (device_name, device_stock, is_use, remarks)
            Cursor.execute(inset_sql)
            DB.commit()
            result = "新增成功！"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": 200, "result": result, "data": []})


class SelectAssetReg(View):
    """ 查询所有资产数据 """

    def post(self, request):
        DB, Cursor = Link_database()
        Asset_registration_Sql = """ select *  from asset_registration """
        Cursor.execute(Asset_registration_Sql)
        AssetData = Cursor.fetchall()
        AssetData_List = []
        for ad in AssetData:
            AssetData_List.append({"id": ad[0], "name": ad[1], "stock": ad[2], "is_use": ad[3], "remarks": ad[4],
                                   "kucun": int(ad[2]) - int(ad[3])})

        DB.close()
        Cursor.close()
        return JsonResponse({"code": 200, "result": "成功！", "data": AssetData_List})


class DeleteAsset(View):
    """ 删除入账账户 """

    def post(self, request):
        id = int(request.POST.get('id'))  # id
        DB, Cursor = Link_database()
        del_Sql = """ DELETE FROM asset_registration WHERE id = %d """ % (id)
        try:
            Cursor.execute(del_Sql)
            DB.commit()
            code = 200
            result = "删除成功!"

        except:
            code = 400
            result = "操作数据库失败!"

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class ResumeView(View):
    """简历视图"""

    def get(self, request):
        """查看简历"""
        user_id = request.GET.get('user')

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": 'id：参数错误', "data": []})

        resume_obj = ResumeSel(user_id)  # 简历实例化

        try:
            # 查询
            i = resume_obj.select_resum()
            if not i:
                return JsonResponse({"code": 400, "result": '此用户无简历', "data": []})
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据整理

            data = {
                "name": i[0],
                "phone": i[1],
                "born_date": i[2],
                "school": i[3],
                "professional": i[4],
                "xz_address": i[5],
                "jt_address": i[6],
                "members": i[7],
                "apply_position": i[8],
                "expected_salary": i[9],
                "work_experience": i[10],
                "education_experience": i[11],
                "opinion": i[12]
            }
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据处理错误', "data": []})

        return JsonResponse({"code": 200, "result": '查询ok', "data": data})

    def post(self, request):
        """录入简历"""
        user_id = request.POST.get('id')
        apply_position = request.POST.get('apply_position')
        expected_salary = request.POST.get('expected_salary')
        work_experience = request.POST.get('work_experience')
        education_experience = request.POST.get('education_experience')
        opinion = request.POST.get('opinion')

        if not all([apply_position, expected_salary, work_experience, education_experience, opinion]):
            return JsonResponse({"code": 400, "result": '缺省参数', "data": []})

        resume_obj = ResumeSQL()  # 简历实例化

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": 'id参数错误', "data": []})

        # try:
        #     # 查询用户id
        #     user_id = resume_obj.select_user(name)
        #     if not user_id:
        #         return JsonResponse({"code": 400, "result": '录入名错误', "data": []})
        # except Exception as e:
        #     logger.error(e)
        #     return JsonResponse({"code": 400, "result": '查询用户id失败', "data": []})

        try:
            resume_obj.entry(apply_position, expected_salary, work_experience, education_experience, opinion, user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '录入失败', "data": []})

        return JsonResponse({"code": 200, "result": '录入成功', "data": []})


class PermissionsView(View):
    """显示可看用户"""

    def get(self, request):
        user_id = request.GET.get('user')

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": 'id：参数错误', "data": []})

        try:
            user_list = [user_id, 0]
            if user_id in SELJIANLI:
                u = alls()
                for i in u:
                    user_list.append(i)
            else:
                us = all_user_id_list(user_id)
                if us:
                    for i in us:
                        user_list.append(i)
            user_id_in = tuple(user_list)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '类型转换错误', "data": []})

        try:
            # 查看用户
            query = select_user(user_id_in)
            if not query:
                return JsonResponse({"code": 400, "result": 'id错误', "data": []})
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据整理
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "name": i[1],
                    "phone": i[2],
                    "department": i[3],
                    "jobs": i[4],
                    "state": i[5],
                    "checked": json.loads(i[6]) if i[6] else []
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据整理错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})


class CreateDepartment(View):
    """ 创建部门 """

    def post(self, request):
        name = request.POST.get('name')
        DB, Cursor = Link_database()
        CheckName_Sql = """ select * from depar where department = '%s' """ % (name)
        Cursor.execute(CheckName_Sql)
        if Cursor.fetchone():
            code = 400
            result = "已有该部门!"
        else:
            inset_sql = """ INSERT INTO  depar(department) VALUES ('%s') """ % (name)
            Cursor.execute(inset_sql)
            DB.commit()
            code = 200
            result = "创建成功!"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class AcquisitionDepartment(View):
    """ 获取部门信息 """

    def post(self, request):
        DB, Cursor = Link_database()
        Asset_registration_Sql = """ select *  from depar """
        Cursor.execute(Asset_registration_Sql)
        AssetData = Cursor.fetchall()
        AssetData_List = []
        for ad in AssetData:
            AssetData_List.append({"id": ad[0], "name": ad[1]})

        DB.close()
        Cursor.close()
        return JsonResponse({"code": 200, "result": "成功！", "data": AssetData_List})


class WorkPlanView(View):
    """工作计划视图"""

    def get(self, request):
        page = request.GET.get('page')
        user_id = request.GET.get('user')

        try:
            user_id = int(user_id)
            page = (int(page) - 1) * 20
            start_time = int(time.mktime(
                time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d'))) - 86400  # 前一天时间,时间戳
            end_time = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        work_obj = WorkPlanSel(user_id, page, start_time, end_time)  # 查看工作计划类

        try:
            # 查询
            query = work_obj.main()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查看失败', "data": []})

        try:
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "statdate": time.strftime('%Y-%m-%d', time.localtime(i[1])),
                    "type": i[2],
                    "department": i[6],
                    "title": i[4],
                    # "content": i[5],
                    "name": i[5]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '组织失败', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        # 接受参数
        user_id = request.POST.get('user')
        is_type = request.POST.get('type')
        department = request.POST.get('department')
        content = request.POST.get('content')
        title = request.POST.get('title')

        statdate = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')))  # 当日时间

        try:
            user_id = int(user_id)
            department = int(department)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": 'id：参数错误', "data": []})

        work_obj = WorkPlanSQL(statdate, is_type, department, title, content, user_id)  # 工作计划录入类

        try:
            # 存储
            work_obj.main()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '录入失败', "data": []})

        return JsonResponse({"code": 200, "result": '录入成功', "data": []})


class SelWorkPlanView(View):
    """不同条件查询工作计划"""

    def get(self, request, pk):
        page = request.GET.get('page')
        user_id = request.GET.get('user')
        department = request.GET.get('department')
        start_time = request.GET.get('start_time')
        end_time = request.GET.get('end_time')

        try:
            page = (int(page) - 1) * 20
            user_id = int(user_id)
            start_time = int(time.mktime(time.strptime(start_time, '%Y-%m-%d')))
            end_time = int(time.mktime(time.strptime(end_time, '%Y-%m-%d')))
            if department:
                department = int(department)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        if pk == '1':
            is_type = '月计划'
        elif pk == '2':
            is_type = '周计划'
        else:
            return JsonResponse({"code": 400, "result": 'url错误', "data": []})

        try:
            # 查询
            sel_obj = sel_work_plan(page, user_id, department, start_time, end_time)
            query = sel_obj.perform(is_type)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "statdate": time.strftime('%Y-%m-%d', time.localtime(i[1])),
                    "type": i[2],
                    "department": i[6],
                    "title": i[4],
                    # "content": i[5],
                    "name": i[5]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '组织失败', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})


class WorkDetailsView(View):
    """工作详情"""

    def get(self, request):
        user_id = request.GET.get('user')
        id = request.GET.get('id')

        try:
            id = int(id)
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 查询
            query = work_details(id, user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据
            data = {
                "id": query[0],
                "statdate": time.strftime('%Y-%m-%d', time.localtime(query[1])),
                "is_type": query[2],
                "department": query[8],
                "title": query[4],
                "content": query[5],
                "notation": json.loads(query[6]) if query[6] else "",
                "name": query[7]
            }
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        id = request.POST.get('id')
        notation = request.POST.get('notation')
        critics = request.POST.get('critics')

        try:
            id = int(id)
            critics = int(critics)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 录入
            WorkDetailsSQL(id, notation, critics)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '录入错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


# class del_WorkPlanView(View):
#     """删除工作计划"""
#
#     def post(self, request, pk):
#         id = request.POST.get('id')
#
#         try:
#             id = int(id)
#         except Exception as e:
#             logger.error(e)
#             return JsonResponse({"code": 400, "result": 'id：参数错误', "data": []})
#
#         work_obj = del_WorkPlanSQL(id)
#
#         if pk == '1':  # 月计划
#             sql = work_obj.month_log()
#         elif pk == '2':  # 周计划
#             sql = work_obj.weeks_log()
#         elif pk == '3':  # 日计划
#             sql = work_obj.job_log()
#         else:
#             return JsonResponse({"code": 400, "result": 'url错误', "data": []})
#
#         try:
#             work_obj.main(sql)
#         except Exception as e:
#             logger.error(e)
#             return JsonResponse({"code": 400, "result": '删除错误', "data": []})
#
#         return JsonResponse({"code": 200, "result": 'OK', "data": []})


class OpenPermissionsView(View):
    """权限开通视图"""

    def get(self, request):
        user_id = request.GET.get('user')

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 查询,数据整理
            open_obj = open_permissions(user_id)
            data = open_obj.deal_with()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询,数据整理错误', "data": []})

        return JsonResponse({"code": 400, "result": '修改成功', "data": data})

    def post(self, request):
        user_id = request.POST.get('user_id')
        checked = request.POST.get('checked')
        sideNavData = request.POST.get('sideNavData')

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        perm_obj = OpenPermissionsSQL(user_id, checked, sideNavData)
        try:
            # 开通
            perm_obj.main()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '开通失败', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class MesageQuery(View):
    """ 消息查询 """

    def post(self, request):
        user_id = int(request.POST.get('user_id'))
        DB, Cursor = Link_database()

        sql = """ SELECT message_table.id,message_table.is_read,message_table.send_id,message_table.files_name,message_table.statdate,message_table.file_id,user.name FROM message_table INNER JOIN user ON message_table.send_id = user.id WHERE message_table.receive_id = %d """ % (
            user_id)
        try:
            Cursor.execute(sql)
            accountData = Cursor.fetchall()
            accountData_List = []
            for ad in accountData:
                if ad[1] == 'False':  # 只取未阅读的数据
                    accountData_List.append(
                        {"id": ad[0], "send_id": ad[2], "title": ad[3], "send_date": ad[4], "file_id": ad[5],
                         "send_name": ad[6]})
            code = 200
            result = "成功！"
            data = accountData_List
        except:
            code = 400
            result = "失败！"
            data = []
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": data})


class MesageStatus(View):
    """ 消息状态修改 """

    def post(self, request):
        DB, Cursor = Link_database()
        id = int(request.POST.get('id'))
        Updata_Sql = """ UPDATE message_table SET is_read = 'True' WHERE  id = %d """ % (id)
        try:
            Cursor.execute(Updata_Sql)
            DB.commit()
            code = 200
            result = "成功！"
            data = []
        except:
            code = 400
            result = "失败！"
            data = []

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": data})


class ExcellToJson(View):
    """ excell转json """

    def post(self, request):
        files_address = request.POST.get('files_address')

        filename = files_address.split("/")[-1]

        files_address = '/home/OA/FinancialStatements/' + filename

        try:
            table_name_list = []
            all_data = []
            data = xlrd.open_workbook(files_address)
            for table in data.sheets():
                table_data = []
                table_name_list.append(table.name)
                nrows = table.nrows
                returnData = {}
                for i in range(nrows):
                    table_data.append(table.row_values(i))
                all_data.append(table_data)
            # all_data[table_name] = table_data
            code = 200
            result = "成功"
            data = {"data": all_data, "sheet": table_name_list}
        except:
            code = 400
            result = "失败"
            data = []
        return JsonResponse({"code": code, "result": result, "data": data})
